﻿namespace Sitecore.ChangesTracker.Archiving
{
  using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Text;
  using System.Threading;
  using Caching;
  using Configuration;
  using Data;
  using Data.Archiving;
  using Data.DataProviders.Sql;
  using Data.Items;
  using Data.Managers;
  using Data.Proxies;
  using Data.SqlServer;
  using Diagnostics;
  using IO;
  using SecurityModel;
  using Sitecore;
  using Sitecore.Data.Events;
  using Sitecore.Data.Fields;
  using Sitecore.Web;
  using Sitecore.ChangesTracker.Utils;
  using Sitecore.ChangesTracker.Exceptions;
  using Sitecore.Data.Templates;
  using Sitecore.ChangesTracker.Events;
    using Sitecore.Caching;

  public class ChangeTrackerArchive : Archive
  {
    protected readonly SqlDataApi _dataApi;
    protected readonly Database _database;
    protected readonly string _connectionstringName;
    protected readonly SqlDataApi _externalDataApi;

    public ChangeTrackerArchive(string name, Database database, string connectionstringName)
      : base(name)
    {
      Assert.ArgumentNotNull(database, "database");
      this._database = database;
      this._dataApi = this.CreateDataApi();
      this._connectionstringName = connectionstringName;
      this._externalDataApi = this.CreateExternalDataApi();
    }
    protected SqlDataApi DataApi
    {
      get
      {
        return this._externalDataApi ?? this._dataApi;
      }
    }

    protected bool UseExternalDataApi
    {
      get
      {
        return this._externalDataApi != null;
      }
    }

    public virtual SqlDataApi CreateExternalDataApi()
    {
      if (string.IsNullOrEmpty(this._connectionstringName))
      {
        return null;
        //throw new InvalidOperationException("External archive database is not set. Archive: " + this.Name+"; Database: "+this._database.Name);
      }

      string connectionString = Sitecore.Configuration.Settings.GetConnectionString(this._connectionstringName);
      if (string.IsNullOrEmpty(connectionString))
      {
        throw new InvalidOperationException("SqlArchive can only be used for databases with a valid ConnectionStringName. Archive: " + this.Name + "; Database: " + this._database.Name);
      }

      return new SqlServerDataApi(connectionString);
    }

    public ArchivedTemplate GetArchivedTempate(ChangeTrackerArchiveEntry entry, bool loadFields = false)
    {
        List<ArchivedItem> items = ArchivedItem.GetItems(entry.ArchivalId, this.DataApi, loadFields);
        return ArchivedTemplate.Parse(entry, items);
    }

    public ArchivedTemplate GetArchivedTempate(Guid archivalId, bool loadFields=false)
    {
        ChangeTrackerArchiveEntry entry = this.GetSingleEntry(archivalId);
        return this.GetArchivedTempate(entry, loadFields);
    }

    public List<Guid> GetItemsToRestore(Guid archivalid)
    {
      ArchiveQuery query=new ArchiveQuery();
      query.ArchivalId = archivalid;
      SqlStatement st = this.GetSelectStatement(query, "{0}ArchivalId{1}, {0}ItemId{1}, {0}ParentId{1}, {0}Name{1}, {0}OriginalLocation{1}, {0}ArchiveDate{1}, {0}ArchivedBy{1}");
      string sql = "select itemid id from archive where archivalid='" + archivalid.ToString() + "'";
      List<Guid> items = new List<Guid>();
      foreach (ChangeTrackerArchiveEntry e in this.GetEntries(st))
      {
        items.Add(e.ItemId.ToGuid());
      }

      //using (DataProviderReader reader = this._dataApi.CreateReader(sql))
      //{
      //  while (reader.Read())
      //  {
      //    items.Add(this._dataApi.GetGuid(0, reader));
      //  }
      //}

      return items;
    }

    public ChangesTracker.Archiving.ChangeTrackerArchiveEntry GetSingleEntry(Guid archivalId)
    {
        ArchiveQuery query = new ArchiveQuery();

        query.ArchivalId = archivalId;
        SqlStatement st = this.GetSelectStatement(query, "{0}ArchivalId{1}, {0}ItemId{1}, {0}ParentId{1}, {0}Name{1}, {0}OriginalLocation{1}, {0}ArchiveDate{1}, {0}ArchivedBy{1}");
        using (DataProviderReader reader = this.DataApi.CreateReader(st.Sql, st.GetParameters()))
        {
            if (reader.Read())
            {
                Guid Id = this.DataApi.GetGuid(0, reader);
                ID itemId = new ID(this.DataApi.GetGuid(1, reader));
                ID parentId = new ID(this.DataApi.GetGuid(2, reader));
                string name = this.DataApi.GetString(3, reader);
                string originalLocation = this.DataApi.GetString(4, reader);
                DateTime dateTime = this.DataApi.GetDateTime(5, reader);
                string archivedBy = this.DataApi.GetString(6, reader);
                return (new ChangeTrackerArchiveEntry(archivalId, itemId, parentId, name, originalLocation, dateTime, archivedBy, Name, this._database));
            }

            return null;
        }

    }

    public bool ItemExists(Guid itemid)
    {

     return this._database.GetItem(ID.Parse(itemid)) != null;

    }

    public void DeleteOldItem(Guid itemid)
    {
      SqlStatement st = new SqlStatement();
      st.Select = "delete";
      st.From = "from {0}VersionedFields{1}";
      st.Where = "Where {0}itemid{1}={2}itemid{3}";
      st.AddParameter("itemid", itemid);
      this._dataApi.Execute(st.Sql, st.GetParameters());

      st = new SqlStatement();
      st.Select = "delete";
      st.From = "from {0}SharedFields{1}";
      st.Where = "Where {0}itemid{1}={2}itemid{3}";
      st.AddParameter("itemid", itemid);
      this._dataApi.Execute(st.Sql, st.GetParameters());

      st = new SqlStatement();
      st.Select = "delete";
      st.From = "from {0}UnversionedFields{1}";
      st.Where = "Where {0}itemid{1}={2}itemid{3}";
      st.AddParameter("itemid", itemid);
      this._dataApi.Execute(st.Sql, st.GetParameters());
    }

    public override bool RestoreItem(Guid archivalId)
    {
      using (new SecurityDisabler())
      {
        if (!this.EnsureRestoreTarget(archivalId))
        {
          return false;
        }
        ChangeTrackerArchiveEntry entry = this.GetSingleEntry(archivalId);
        Assert.ArgumentNotNull(entry, "entry");
        Database db = Sitecore.Configuration.Factory.GetDatabase(entry.Database);
        if (entry.Reason.Equals("created"))
        {
          using (new ArchiveDisabler(entry.ItemId.Guid))
          {
            ItemManager.DeleteItem(db.GetItem(entry.ItemId));
          }
        }
        else
        {

          Item itm = db.GetItem(ID.Parse(entry.ItemId));
          List<ArchivedItem> items= this.GetArchivedItems(entry.ArchivalId);
          using (DataProviderTransaction transaction = this._dataApi.CreateTransaction())
          {
            
            foreach (ArchivedItem ai in items)
            {
              
              if ((itm == null && !entry.OriginalLocation.StartsWith("/sitecore/templates/",StringComparison.OrdinalIgnoreCase)) || (!TemplateManager.IsTemplate(itm) && !TemplateManager.IsTemplatePart(itm)))
              {

                TemplateItem ti = db.GetTemplate(ID.Parse(ai.TemplateId));
                if (ti != null && !Utility.GetItemTemplatesHash(ti).Equals(entry.Templates))
                {
                  throw new TemplateChangedException("Item tmplate was changed or deleted sicne this control point had been created", entry.OriginalLocation);
                }
              }

              this.RestoreItemData(archivalId, entry.Database, ai.ItemId);
            }

            this.RestoreFieldData(archivalId, entry.Database);
            transaction.Complete();
          }
          if (itm!=null&&TemplateManager.IsTemplate(itm))
          {
            Template t = TemplateManager.GetTemplate(itm.ID, itm.Database);
            TemplateField[] fields=t.GetFields(false);
            for (int i = 0; i < fields.Length; ++i)
            {
              if (items.FirstOrDefault(f => f.ItemId == fields[i].ID.ToGuid())==null)
              {
                Item item2=itm.Database.GetItem(fields[i].ID);
                using (new ArchiveDisabler(TemplateTracker.FindTemplate(item2).ID.ToGuid()))
                {
                  
                  ItemManager.DeleteItem(item2,SecurityCheck.Disable);
                }
              }
            }
          }
        }

        this.UnregisterArchival(archivalId);
        if (entry.IsTemplate)
        {
            db.Engines.TemplateEngine.Reset();
        }
        CacheManager.ClearAllCaches();
        return true;
      }
    }
    
    void RestoreToOldItem(Guid itemId, Guid archivalId)
    {
      
      using (new ArchiveDisabler(itemId))
      {
        Item target = this._database.GetItem(ID.Parse(itemId));

        ArchivedItem item = this.GetArchivedItems(archivalId).FirstOrDefault(i=>i.ItemId==itemId);

        Assert.ResultNotNull(item, "Item not found id:" + itemId.ToString());
          target.Editing.BeginEdit();
          if (target.Name != item.Name)
          {
            target.Name = item.Name;
          }
          if (target.TemplateID.ToGuid() != item.TemplateId)
          {
            target.ChangeTemplate(target.Database.GetTemplate(ID.Parse(item.TemplateId)));
          }
          if (target.ParentID.ToGuid() != item.ParentId)
          {
            target.MoveTo(this._database.GetItem(ID.Parse(item.ParentId)));
          }
          target.Editing.EndEdit();
          this.DeleteOldItem(itemId);
      }

    }

    public Guid ArchiveItem(Item item, string info)
    {
      Assert.ArgumentNotNull(item, "item");
      if (!item.Access.CanDelete())
      {
        return Guid.Empty;
      }

      Guid archivalId = Guid.NewGuid();
      using (new SecurityDisabler())
      {
        if (item.RuntimeSettings.IsVirtual)
        {
          item = ProxyManager.GetRealItem(item, false);
        }

        this.RegisterArchival(item, archivalId,info);
        if (!this.DoArchiveItems(new Item[] { item }, archivalId))
        {
          return Guid.Empty;
        }
      }

      //CacheManager.ClearAllCaches();
      return archivalId;
    }

    public override Guid ArchiveItem(Item item)
    {
      return this.ArchiveItem(item, null);
    }

    public override Guid GetArchivalId(ID itemId)
    {
      Assert.ArgumentNotNull(itemId, "itemId");
      ArchiveQuery query = new ArchiveQuery();
      query.ItemId = itemId;
      SqlStatement selectStatement = this.GetSelectStatement(query, "{0}ArchivalId{1}");
      if (selectStatement == null)
      {
        return Guid.Empty;
      }

      return this.GetGuid(selectStatement.Sql, selectStatement.GetParameters(), Guid.Empty);
    }

    public virtual List<ArchivedItem> GetArchivedItems(Guid archivalId,bool loadFields=false)
    {
      return ArchivedItem.GetItems(archivalId, this.DataApi, loadFields);
    } 

    public IEnumerable<ChangeTrackerArchiveEntry> GetDateEntriesForUser(Sitecore.Security.Accounts.User user, int pageIndex, int pageSize, DateTime startDate, DateTime endDate)
    {
      Assert.ArgumentNotNull(user, "user");
        ArchiveQuery query = new ArchiveQuery();
        if (!user.IsAdministrator && !user.IsInRole(@"sitecore\Changes Tracking Admin"))
        {
            query.ArchivedBy = user.Name + "|%";
        }
        query.ArchiveDateBegin = startDate;
        query.ArchiveDateEnd = endDate;
        query.OriginalLocation = this._database.Name + ":%";
        return this.GetEntries(query, pageIndex, pageSize).Cast<ChangeTrackerArchiveEntry>();
    }

    public override IEnumerable<ArchiveEntry> GetEntries(ArchiveQuery query, int pageIndex, int pageSize)
    {
      Assert.ArgumentNotNull(query, "query");
      SqlStatement statement = this.GetSelectStatement(
                                                       query,
                                                       "{0}ArchivalId{1}, {0}ItemId{1}, {0}ParentId{1}, {0}Name{1}, {0}OriginalLocation{1}, {0}ArchiveDate{1}, {0}ArchivedBy{1}",
                                                       pageIndex,
                                                       pageSize
                                                       );
      return this.GetEntries(statement).Cast<ArchiveEntry>();
    }

    public override void RemoveEntries(ArchiveQuery query)
    {
      Assert.ArgumentNotNull(query, "query");
      SqlStatement selectStatement = this.GetSelectStatement(query, "{0}ArchivalId{1}");
      using (DataProviderTransaction transaction = this.DataApi.CreateTransaction())
      {
        string[] strArray = this.UseExternalDataApi ? new string[] {"{0}blobs{1}","{0}ArchivedItems{1}", "{0}ArchivedFields{1}", "{0}Archive{1}" } : new string[] { "{0}ArchivedItems{1}", "{0}ArchivedFields{1}", "{0}Archive{1}" };
        foreach (string str in strArray)
        {
          SqlStatement statement2 = new SqlStatement();
          statement2.Select = "DELETE";
          statement2.From = " FROM " + str + "";
          statement2.Where = " WHERE {0}ArchivalId{1} IN (" + selectStatement.Sql + ")";
          statement2.AddParameters(selectStatement.GetParameters());
          this.DataApi.Execute(statement2.Sql, statement2.GetParameters());
        }

        transaction.Complete();
      }
    }

    //public IEnumerable<ChangeTrackerArchiveEntry> GetEntries(ArchiveQuery query, int pageIndex, int pageSize)
    //{
    //  Assert.ArgumentNotNull(query, "query");
    //  SqlStatement statement = this.GetSelectStatement(
    //                                                   query,
    //                                                   "{0}ArchivalId{1}, {0}ItemId{1}, {0}ParentId{1}, {0}Name{1}, {0}OriginalLocation{1}, {0}ArchiveDate{1}, {0}ArchivedBy{1}",
    //                                                   pageIndex,
    //                                                   pageSize
    //                                                   );
    //  return this.GetEntries(statement);
    //}

    //public SqlStatement GetOldEntries(int daysToKeep)
    //{
    //  Assert.ArgumentNotNull(daysToKeep, "DaysToKeep");
    //  SqlStatement statement = new SqlStatement();
    //  statement.Select = "SELECT {0}archivalid{1}";
    //  statement.From = "FROM {0}archive{1}";
    //  statement.Where =
    //    "WHERE DATEDIFF(\"dd\",{0}archivedate{1},'" + DateTime.Today.ToString() + "')>'" + daysToKeep.ToString() +
    //    "' and {0}archivename{1}='" + this.Name + "'";
    //  return statement;
    //}

    //public void RemoveOldEntries(int age)
    //{
    //  SqlStatement st = this.GetOldEntries(age);
      
    //  using (DataProviderTransaction transaction = this.DataApi.CreateTransaction())
    //  {
    //    string[] strArray = new string[] { "ArchivedItems", "ArchivedFields", "Archive" };
    //    foreach (string str in strArray)
    //    {
    //      SqlStatement statement2 = new SqlStatement();
    //      statement2.Select = "DELETE";
    //      statement2.From = " FROM {0}" + str + "{1}";
    //      statement2.Where = " WHERE {0}ArchivalId{1} IN (" + st.Sql + ")";
    //      statement2.AddParameters(st.GetParameters());
    //      this.DataApi.Execute(statement2.Sql, statement2.GetParameters());
    //    }

    //    transaction.Complete();
    //  }
    //}

    public override int GetEntryCount(ArchiveQuery query)
    {
      Assert.ArgumentNotNull(query, "query");
      SqlStatement selectStatement = this.GetSelectStatement(query, "count(1)");
      if (selectStatement == null)
      {
        return -1;
      }

      return this.GetInt(selectStatement.Sql, selectStatement.GetParameters(), -1);
    }

    protected virtual void AddDate(DateTime value, string columnName,string paramname, string comparisonOperator, StringBuilder whereClause, SqlStatement statement)
    {
      if (value != Constants.NullDate)
      {
        string parameterName =string.IsNullOrEmpty(paramname)? this.GetParameterName(columnName):paramname;
        whereClause.Append(" AND {0}" + columnName + "{1} " + comparisonOperator + " {2}" + parameterName + "{3}");
        statement.AddParameter(parameterName, value);
      }
    }

    protected virtual void AddGuid(Guid id, string columnName, StringBuilder whereClause, SqlStatement statement)
    {
      if (id != Guid.Empty)
      {
        string parameterName = this.GetParameterName(columnName);
        whereClause.Append(" AND {0}" + columnName + "{1} = {2}" + parameterName + "{3}");
        statement.AddParameter(parameterName, id);
      }
    }

    protected virtual void AddId(ID id, string columnName, StringBuilder whereClause, SqlStatement statement)
    {
      if (!ID.IsNullOrEmpty(id))
      {
        string parameterName = this.GetParameterName(columnName);
        whereClause.Append(" AND {0}" + columnName + "{1} = {2}" + parameterName + "{3}");
        statement.AddParameter(parameterName, id.ToGuid());
      }
    }

    protected virtual void AddOrderByClause(ArchiveQuery query, SqlStatement statement)
    {
      StringBuilder builder = new StringBuilder();
      if (query.Orderings.Count != 0)
      {
        foreach (ArchiveQuery.Ordering ordering in query.Orderings)
        {
          if (builder.Length == 0)
          {
            builder.Append("ORDER BY ");
          }
          else
          {
            builder.Append(", ");
          }

          builder.Append("{0}");
          builder.Append(ordering.Field.ToString());
          builder.Append("{1}");
          if (ordering.Direction == ArchiveQuery.OrderDirection.Descending)
          {
            builder.Append(" DESC");
          }
        }

        statement.OrderBy = builder.ToString();
      }
    }

    protected virtual void AddString(string value, string columnName, StringBuilder whereClause, SqlStatement statement)
    {
      if (value != null)
      {
        string parameterName = this.GetParameterName(columnName);
        whereClause.Append(" AND {0}" + columnName + "{1} LIKE {2}" + parameterName + "{3}");
        statement.AddParameter(parameterName, value);
      }
    }

    protected virtual void AddWhereClause(ArchiveQuery query, SqlStatement statement)
    {
      StringBuilder whereClause = new StringBuilder();
      whereClause.Append("WHERE {0}ArchiveName{1} = {2}archiveName{3}");
      statement.AddParameter("archiveName", Name);
      if (!query.IsEmpty)
      {
        this.AddGuid(query.ArchivalId, "ArchivalId", whereClause, statement);
        this.AddId(query.ItemId, "ItemId", whereClause, statement);
        this.AddId(query.ParentId, "ParentId", whereClause, statement);
        this.AddDate(query.ArchiveDateBegin, "ArchiveDate","ArchiveDateBegin", ">=", whereClause, statement);
        this.AddDate(query.ArchiveDateEnd, "ArchiveDate","ArchiveDateEnd", "<=", whereClause, statement);
        this.AddString(query.ArchivedBy, "ArchivedBy", whereClause, statement);
        this.AddString(query.Name, "Name", whereClause, statement);
        this.AddString(query.OriginalLocation, "OriginalLocation", whereClause, statement);
      }

      statement.Where = whereClause.ToString();
    }

    protected virtual SqlDataApi CreateDataApi()
    {
      string connectionStringName = this._database.ConnectionStringName;
      //if(!string.IsNullOrEmpty(this._connectionstringName))
      //{
      //  connectionStringName = this._connectionstringName;
      //}
      if (string.IsNullOrEmpty(connectionStringName))
      {
        throw new InvalidOperationException("SqlArchive can only be used for databases with a valid ConnectionStringName. Database: " + this._database.Name);
      }

      string connectionString = Sitecore.Configuration.Settings.GetConnectionString(connectionStringName);
      if (string.IsNullOrEmpty(connectionString))
      {
        throw new InvalidOperationException("SqlArchive can only be used for databases with a valid ConnectionStringName. Database: " + this._database.Name);
      }

      return new SqlServerDataApi(connectionString);
    }

    protected virtual IEnumerable<ChangeTrackerArchiveEntry> GetEntries(SqlStatement statement)
    {
      List<ChangeTrackerArchiveEntry> list = new List<ChangeTrackerArchiveEntry>();
      using (DataProviderReader reader = this.DataApi.CreateReader(statement.Sql, statement.GetParameters()))
      {
        while (reader.Read())
        {
          Guid archivalId = this.DataApi.GetGuid(0, reader);
          ID itemId = new ID(this.DataApi.GetGuid(1, reader));
          ID parentId = new ID(this.DataApi.GetGuid(2, reader));
          string name = this.DataApi.GetString(3, reader);
          string originalLocation = this.DataApi.GetString(4, reader);
          DateTime dateTime = this.DataApi.GetDateTime(5, reader);
          string archivedBy = this.DataApi.GetString(6, reader);
          list.Add(new ChangeTrackerArchiveEntry(archivalId, itemId, parentId, name, originalLocation, dateTime, archivedBy, Name, this._database));
        }
        
      }

      return list;
    }

    protected virtual string GetParameterName(string columnName)
    {
      return columnName.Substring(0, 1).ToLower() + columnName.Substring(1);
    }

    protected virtual SqlStatement GetSelectStatement(ArchiveQuery query, string columns)
    {
      SqlStatement statement = new SqlStatement();
      statement.Select = "SELECT " + columns;
     // string extdb = GetStorage() + "{0}Archive{1}";
      statement.From = "FROM {0}Archive{1}";
      this.AddWhereClause(query, statement);
      this.AddOrderByClause(query, statement);
      return statement;
    }

    protected virtual SqlStatement GetSelectStatement(ArchiveQuery query, string columns, int pageIndex, int pageSize)
    {
      SqlStatement statement = new SqlStatement();
      SqlStatement statement2 = new SqlStatement();
      int num = (pageIndex * pageSize) + 1;
      int num2 = (pageSize == 0x7fffffff) ? 0x7fffffff : ((pageIndex + 1) * pageSize);
      this.AddWhereClause(query, statement);
      this.AddOrderByClause(query, statement2);
      if (statement2.OrderBy.Length == 0)
      {
        statement2.OrderBy = "ORDER BY {0}ArchiveDate{1} DESC, {0}ArchivalId{1}";
      }

      statement.Select = "SELECT " + columns + ", ROW_NUMBER() OVER (" + statement2.OrderBy + ") AS {0}RowNumber{1}";
      //string extdb = GetStorage() + "{0}Archive{1}";
      statement.From = "FROM {0}Archive{1}";
      statement2.Select = "SELECT " + columns;
      statement2.From = "FROM (" + statement.Sql + ") Data";
      //statement2.Where =
      //  "WHERE {0}RowNumber{1} BETWEEN {2}firstRow{3} AND {2}lastRow{3} and YEAR({0}ArchiveDate{1})='" + startDate.Year +
      //  "' and MONTH({0}ArchiveDate{1})='" + startDate.Month + "' and DAY({0}ArchiveDate{1})='" + startDate.Day + "'";
      statement2.Where = "WHERE {0}RowNumber{1} BETWEEN {2}firstRow{3} AND {2}lastRow{3}";
      statement2.AddParameter("firstRow", num);
      statement2.AddParameter("lastRow", num2);
      object[] parameters = statement.GetParameters();
      for (int i = 0; i < (parameters.Length - 1); i += 2)
      {
        statement2.AddParameter((string)parameters[i], parameters[i + 1]);
      }

      return statement2;
    }

    private void RegisterArchival (Item item, Guid archivalId,string info = null)
    {
      //SqlDataApi api = this._externalDataApi ?? this._dataApi;
      using (DataProviderTransaction transaction = this.DataApi.CreateTransaction())
      {
        string archived = Context.GetUserName();
        if(!string.IsNullOrEmpty(info))
        {
           archived+="|"+info;
        }
        //var extdb = GetStorage() + "{0}Archive{1}";
        string location = this._database.Name + ":" + item.Paths.Path + ":" + Utility.GetItemTemplatesHash(item.Template);
        if (TemplateManager.IsTemplate(item))
        {
            location += ":true";
        }
        else
        {
            location += ":false";
        }
        string sql = " INSERT INTO {0}Archive{1} ({0}ArchivalId{1}, {0}ItemId{1}, {0}ParentId{1}, {0}Name{1}, {0}OriginalLocation{1}, {0}ArchiveDate{1}, {0}ArchivedBy{1}, {0}ArchiveName{1})VALUES ({2}archivalId{3}, {2}itemId{3}, {2}parentId{3}, {2}name{3}, {2}originalLocation{3}, {2}archiveDate{3}, {2}archivedBy{3}, {2}archiveName{3}\r\n                    )";
        object[] par = new object[]
        {
          "archivalId", archivalId, "itemId", item.ID.ToGuid(), "parentId", item.ParentID.ToGuid(), "name", item.Name, "originalLocation", location, "archiveDate", DateTime.UtcNow, "archivedBy", archived, "archiveName", this.Name
        };
        this.DataApi.Execute(sql,par);
        transaction.Complete();
      }
    }

    //private static string GetStorage()
    //{
    //  string extdb = "";
    //  if (Settings.UseExternalStorage)
    //  {
    //    SqlConnection con = new SqlConnection(Settings.GetExternalConnectionstring);
    //    extdb = "{0}" + con.Database + "{1}.{0}dbo{1}.";
    //  }
    //  return extdb;
    //}

    private void RestoreFieldData(Guid archivalId, string databaseName)
    {
      string[] strArray = new string[] { "{0}SharedFields{1}", string.Empty, "shared", "{0}UnversionedFields{1}", "{0}Language{1}, ", "unversioned", "{0}VersionedFields{1}", "{0}Language{1}, {0}Version{1}, ", "versioned" };

      for (int i = 0; i < (strArray.Length - 2); i += 3)
      {
        string str = strArray[i];
        string str2 = strArray[i + 1];
        string str3 = strArray[i + 2];
        if (this.UseExternalDataApi)
        {
          this.RestoreFieldDataExternal(archivalId, str, str2, str3);
        }
        else
        {
          string created = Settings.RestoreItemCreatedDate ? "{0}Created{1}" : "{2}" + DateTime.UtcNow + "{3}";
          string updated = Settings.ResoreItemUpdatedDate ? ",{0}Updated{1}" : ",{2}" + DateTime.UtcNow + "{3}";
          //string sql = "INSERT INTO " + str + " ({0}Id{1}, {0}ItemId{1}, " + str2 + " {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} ) SELECT {0}RowId{1}, {0}ItemId{1}, " + 
          //  str2 + " {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} FROM {0}ArchivedFields{1} WHERE {0}ArchivalId{1} = {2}archivalId{3} AND {0}SharingType{1} = {2}sharingType{3}";
          string sql = "INSERT INTO " + str + " ({0}Id{1}, {0}ItemId{1}, " + str2 + " {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} ) SELECT {0}RowId{1}, {0}ItemId{1}, " +
           str2 + " {0}FieldId{1}, {0}Value{1}, "+created+updated+" FROM {0}ArchivedFields{1} WHERE {0}ArchivalId{1} = {2}archivalId{3} AND {0}SharingType{1} = {2}sharingType{3}";
          this._dataApi.Execute(sql, new object[]
          {
            "archivalId", archivalId, "sharingType", str3
          });
        }
      }
    }

    private void RestoreFieldDataExternal(Guid archivalId, string str, string str2, string str3)
    {
      string select = "SELECT {0}RowId{1}, {0}ItemId{1}, {0}Language{1}, {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1},{0}version{1} FROM {0}ArchivedFields{1} WHERE {0}ArchivalId{1} = {2}archivalId{3} AND {0}SharingType{1} = {2}sharingType{3}";
      using (DataProviderReader reader = this.DataApi.CreateReader(@select, new object[]
      {
        "archivalId", archivalId, "SharingType", str3
      }))
      {
        while (reader.Read())
        {
          Guid rowid = this.DataApi.GetGuid(0, reader);
          Guid itemid = this.DataApi.GetGuid(1, reader);
          string language = this.DataApi.GetString(2, reader);
          Guid fieldid = this.DataApi.GetGuid(3, reader);
          string value = this.DataApi.GetString(4, reader);
          DateTime created = Settings.RestoreItemCreatedDate?this.DataApi.GetDateTime(5, reader):DateTime.UtcNow;
          DateTime updated =Settings.ResoreItemUpdatedDate?this.DataApi.GetDateTime(6, reader):DateTime.UtcNow;
          int version = this.DataApi.GetInt(7, reader);
          string str4 = str2.Replace("{0}", "{2}").Replace("{1}", "{3}");
          string insert = " INSERT INTO " + str + " ({0}Id{1}, {0}ItemId{1}, " + str2 + " {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1}";

          insert += ") values({2}Id{3}, {2}ItemId{3}";
          if (!string.IsNullOrEmpty(str4))
          {
            insert += "," + str4.Remove(str2.LastIndexOf(','), 1);
          }
          insert += ",{2}FieldId{3},{2}Value{3}, {2}Created{3}, {2}Updated{3}";

          insert += ")";

          this._dataApi.Execute(insert, new object[]
          {
            "Id", rowid, "ItemId", itemid, "Language", language, "FieldId", fieldid, "Value", value, "Created", created, "Updated", updated, "Version", version
          });
        
        }
      }
      this.RestoreBlobs(archivalId);
    }

    private void RestoreBlobs(Guid archivalId)
    {
      SqlStatement selectblobs = new SqlStatement();
      selectblobs.Select = "SELECT {0}Id{1},{0}BlobId{1}, {0}Index{1}, {0}Data{1}, {0}Created{1}";
      selectblobs.From = "from {0}blobs{1}";
      selectblobs.Where = "where {0}archivalid{1}={2}archivalid{3}";
      selectblobs.AddParameter("archivalid", archivalId);
      using (DataProviderReader reader1 = this.DataApi.CreateReader(selectblobs.Sql, selectblobs.GetParameters()))
      {
        while (reader1.Read())
        {
          Guid id1 = this.DataApi.GetGuid(0, reader1);
          Guid blobid = this.DataApi.GetGuid(1, reader1);
          int index = this.DataApi.GetInt(2, reader1);
          byte[] buf = reader1.InnerReader.GetValue(3) as byte[];
          DateTime created1 = this.DataApi.GetDateTime(4, reader1);
          SqlStatement insertblobs = new SqlStatement();
          insertblobs.Select = "insert into {0}blobs{1} ({0}id{1}, {0}BlobId{1}, {0}Index{1}, {0}Data{1}, {0}Created{1}) ";
          insertblobs.From = "values ({2}id{3}, {2}blobid{3}, {2}index{3}, {2}data{3}, {2}created{3})";
          insertblobs.AddParameters(new object[]
          {
            "id", id1, "blobid", blobid, "index", index, "data", buf, "created", created1
          });
          string delete = "delete from {0}blobs{1} where {0}blobid{1}={2}blobid{3}";
          this._dataApi.Execute(delete, new object[]
          {
            "blobid", blobid
          });
          this._dataApi.Execute(insertblobs.Sql, insertblobs.GetParameters());
        }
      }
    }

    private void RestoreItemData(Guid archivalId, string databaseName, Guid itemId)
    {
      //string extdb = GetStorage() + "{0}ArchivedItems{1}";
     // string str = "{0}" + databaseName + "{1}.{0}dbo{1}.{0}Items{1}";
     
        if (this.ItemExists(itemId))
        {

          this.RestoreToOldItem(itemId, archivalId);
          return;
        }
        if (this.UseExternalDataApi)
        {
          this.RestoreItemDataExternal(archivalId,itemId);
        }
        else
        {

            string sql = " INSERT INTO {0}Items{1} ({0}ID{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1}) SELECT {0}ItemId{1}, {0}Name{1},  {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1} FROM {0}ArchivedItems{1} WHERE {0}ArchivalId{1} = {2}archivalId{3} and {0}ItemId{1}={2}itemid{3}";
            this._dataApi.Execute(sql, new object[] { "archivalId", archivalId, "itemid", itemId });

        }
      
    }

    private void RestoreItemDataExternal(Guid archivalId, Guid itemId)
    {
      string select = "SELECT {0}ItemId{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1} FROM {0}ArchivedItems{1} WHERE {0}ArchivalId{1} = {2}archivalId{3} and {0}ItemId{1}={2}itemid{3}";
      using (DataProviderReader reader = this.DataApi.CreateReader(@select, new object[]
      {
        "archivalId", archivalId,"itemid", itemId
      }))
      {
        while (reader.Read())
        {
          Guid itemid = this.DataApi.GetGuid(0, reader);
          string name = this.DataApi.GetString(1, reader);          
          Guid templateid = this.DataApi.GetGuid(2, reader);
          Guid masterid = this.DataApi.GetGuid(3, reader);
          Guid parentid = this.DataApi.GetGuid(4, reader);
          DateTime created = this.DataApi.GetDateTime(5, reader);
          DateTime updated = this.DataApi.GetDateTime(6, reader);
          string insert = " INSERT INTO {0}Items{1} ({0}ID{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1}) values({2}ItemId{3}, {2}Name{3}, {2}TemplateID{3}, {2}MasterID{3}, {2}ParentID{3}, {2}Created{3}, {2}Updated{3})";
          this._dataApi.Execute(insert, new object[]
          {
            "ItemId", itemid, "Name", name, "TemplateID", templateid, "MasterID", masterid, "ParentID", parentid, "Created", created, "Updated", updated
          });
        }
      }
    }

    private void UnregisterArchival(Guid archivalId)
    {
      ArchiveQuery query = new ArchiveQuery();
      query.ArchivalId = archivalId;
      this.RemoveEntries(query);
    }

    private bool ArchiveData(Item item, Guid archivalId)
    {
      this.ArchiveFieldData(item, archivalId);
      this.ArchiveItemData(item, archivalId);

      return true;
    }
    
    private void ArchiveFieldData(Item item, Guid archivalId)
    {
      string[] strArray = new string[] { "SharedFields", "''", "0", "shared", "UnversionedFields", "{0}Language{1}", "0", "unversioned", "VersionedFields", "{0}Language{1}", "{0}Version{1}", "versioned" };
      for (int i = 0; i < (strArray.Length - 3); i += 4)
      {
        string str = strArray[i];
        string str2 = strArray[i + 1];
        string str3 = strArray[i + 2];
        string str4 = strArray[i + 3];
        //string extdb = GetStorage() + "{0}ArchivedFields{1}";
        if (this.UseExternalDataApi)
        {
          this.ArchiveFieldDataExternal(item, archivalId, str3, str2, str, str4);
        }
        else
        {
          string sql = " INSERT INTO {0}ArchivedFields{1} ({0}RowId{1}, {0}ArchivalId{1}, {0}SharingType{1}, {0}ItemId{1}, {0}Language{1}, {0}Version{1}, {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1}) SELECT {0}Id{1}, {2}archivalId{3}, {2}sharingType{3}, {0}ItemId{1}, " + str2 + ", " + str3 + ", {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} FROM {0}" + str + "{1} WHERE {0}ItemId{1} = {2}itemId{3}";
          this._dataApi.Execute(sql, new object[] { "archivalId", archivalId, "sharingType", str4, "itemId", item.ID.ToGuid() });
        }
      }
    }

    private void ArchiveFieldDataExternal(Item item, Guid archivalId, string str3, string str2, string str, string str4)
    {
      string select = "SELECT {0}Id{1}, {0}ItemId{1}," + str2 + ", " + str3 + ", {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} FROM {0}" + str + "{1} WHERE {0}ItemId{1} = {2}itemId{3}";
      using (DataProviderTransaction transaction = this.DataApi.CreateTransaction())
      {
        List<string> values=new List<string>();
      using (DataProviderReader reader = this._dataApi.CreateReader(@select, new object[]
      {
        "itemId", item.ID.ToGuid()
      }))
      {
        
          while (reader.Read())
          {
            Guid id = this._dataApi.GetGuid(0, reader);
            Guid itemid = this._dataApi.GetGuid(1, reader);
            string language = this._dataApi.GetString(2, reader);
            int version = this._dataApi.GetInt(3, reader);
            Guid fieldid = this._dataApi.GetGuid(4, reader);
            string value = this._dataApi.GetString(5, reader);
            DateTime created = this._dataApi.GetDateTime(6, reader);
            DateTime updated = this._dataApi.GetDateTime(7, reader);

            Field f = item.Fields[ID.Parse(fieldid)];
            if (f.IsBlobField && Settings.ArchiveBlobData)
            {
              values.Add(value);
            }

            string insert = " INSERT INTO {0}ArchivedFields{1} ({0}RowId{1}, {0}ArchivalId{1}, {0}SharingType{1}, {0}ItemId{1}, {0}Language{1}, {0}Version{1}, {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1}) values(" +
              "{2}Id{3},{2}archivalId{3},{2}sharingType{3},{2}ItemId{3},{2}Language{3},{2}Version{3},{2}FieldId{3}, {2}Value{3}, {2}Created{3}, {2}Updated{3})";
            this.DataApi.Execute(insert, new object[]
            {
              "Id", id, "archivalId", archivalId, "sharingType", str4, "ItemId", itemid, "Language", language, "Version", version, "FieldId", fieldid, "Value", value, "Created", created, "Updated", updated
            });
            //SELECT {0}Id{1}, {2}archivalId{3}, {2}sharingType{3}, {0}ItemId{1}, " + str2 + ", " + str3 + ", {0}FieldId{1}, {0}Value{1}, {0}Created{1}, {0}Updated{1} ;
          }
          
        }
        foreach (string val in values)
        {
          if (ID.IsID(val))
          {
            this.ArchiveBlobs(archivalId, val);
          }
        }
     
      transaction.Complete();
      }
    }

    private void ArchiveBlobs(Guid archivalId, string value)
    {
      
      
        SqlStatement selectblobs = new SqlStatement();
        selectblobs.Select = "SELECT {0}Id{1}, {0}Index{1}, {0}Data{1}, {0}Created{1}";
        selectblobs.From = "from {0}blobs{1}";
        selectblobs.Where = "where {0}blobid{1}={2}blobid{3}";
        selectblobs.AddParameter("blobid", new Guid(value));
        using (DataProviderReader reader1 = this._dataApi.CreateReader(selectblobs.Sql, selectblobs.GetParameters()))
        {
          while (reader1.Read())
          {
            Guid id1 = this._dataApi.GetGuid(0, reader1);
            int index = this._dataApi.GetInt(1, reader1);
            byte[] buf = reader1.InnerReader.GetValue(2) as byte[];
            DateTime created1 = this._dataApi.GetDateTime(3, reader1);
            SqlStatement insertblobs = new SqlStatement();
            insertblobs.Select = "insert into {0}blobs{1} ({0}id{1}, {0}BlobId{1}, {0}Index{1}, {0}Data{1}, {0}Created{1}, {0}ArchivalId{1}) ";
            insertblobs.From = "values ({2}id{3}, {2}blobid{3}, {2}index{3}, {2}data{3}, {2}created{3}, {2}archivalid{3})";
            insertblobs.AddParameters(new object[]
            {
              "id", id1, "blobid", new Guid(value), "index", index, "data", buf, "created", created1, "archivalid", archivalId
            });
            this.DataApi.Execute(insertblobs.Sql, insertblobs.GetParameters());
          }
        

        // string insertblob = "insert into {0}Blobs{1} (";
      }
    }

    private void ArchiveItemData(Item item, Guid archivalId)
    {
      //string extdb = GetStorage() + "{0}ArchivedItems{1}";
      if (this.UseExternalDataApi)
      {
        this.ArchiveItemDataExternal(item, archivalId);
      }
      else
      {
        string sql = " INSERT INTO {0}ArchivedItems{1} ({0}RowId{1}, {0}ArchivalId{1}, {0}ItemId{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1}) SELECT {2}new_id{3}, {2}archivalId{3}, {0}ID{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1} FROM {0}Items{1}   WHERE {0}ID{1} = {2}itemId{3}";
        this._dataApi.Execute(sql, new object[] { "new_id", Guid.NewGuid(), "archivalId", archivalId, "itemId", item.ID.ToGuid() });
      }

    }

    private void ArchiveItemDataExternal(Item item, Guid archivalId)
    {
      string select = "SELECT {0}ID{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1} FROM {0}Items{1} WHERE {0}ID{1} = {2}itemId{3}";
      using (DataProviderReader reader = this._dataApi.CreateReader(@select, new object[]
      {
        "itemId", item.ID.ToGuid()
      }))
      {
        using (DataProviderTransaction transaction = this.DataApi.CreateTransaction())
        {
          while (reader.Read())
          {
            Guid id = this._dataApi.GetGuid(0, reader);
            string name = this._dataApi.GetString(1, reader);
            Guid templateid = this._dataApi.GetGuid(2, reader);
            Guid masterid = this._dataApi.GetGuid(3, reader);
            Guid parentid = this._dataApi.GetGuid(4, reader);
            DateTime created = this._dataApi.GetDateTime(5, reader);
            DateTime updated = this._dataApi.GetDateTime(6, reader);

            string insert = " INSERT INTO {0}ArchivedItems{1} (" +
              "{0}RowId{1}, {0}ArchivalId{1}, {0}ItemId{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1}) values({2}new_id{3},{2}achivalid{3},{2}id{3},{2}name{3},{2}templateid{3},{2}masterid{3},{2}parentid{3},{2}created{3},{2}updated{3})";
            this.DataApi.Execute(insert, new object[]
            {
              "new_id", Guid.NewGuid(), "achivalid", archivalId, "id", id, "name", name, "templateid", templateid, "masterid", masterid, "parentid", parentid, "created", created, "Updated", updated
            });
            // " SELECT {2}new_id{3}, {2}archivalId{3}, {0}ID{1}, {0}Name{1}, {0}TemplateID{1}, {0}MasterID{1}, {0}ParentID{1}, {0}Created{1}, {0}Updated{1} ";
          }
          transaction.Complete();
        }
      }
    }

    private Guid GetGuid(string sql, object[] parameters, Guid defaultValue)
    {
      using (DataProviderReader reader = this._dataApi.CreateReader(sql, parameters))
      {
        if (!reader.Read())
        {
          return defaultValue;
        }

        return this._dataApi.GetGuid(0, reader);
      }
    }

    private int GetInt(string sql, object[] parameters, int defaultValue)
    {
      using (DataProviderReader reader = this._dataApi.CreateReader(sql, parameters))
      {
        if (!reader.Read())
        {
          return defaultValue;
        }

        return this._dataApi.GetInt(0, reader);
      }
    }

    private bool CreateRestoreTarget(ID targetId, string originalLocation)
    {
      TemplateItem template = this._database.Templates[TemplateIDs.Folder];
      if (template == null)
      {
        return false;
      }

      string parentPath = FileUtil.GetParentPath(originalLocation);
      if (parentPath.Length == 0)
      {
        return false;
      }

      string path = FileUtil.GetParentPath(parentPath);
      if (path.Length == 0)
      {
        return false;
      }

      Item destination = this._database.CreateItemPath(path, template);
      if (destination == null)
      {
        return false;
      }

      return ItemManager.CreateItem(FileUtil.GetFileName(parentPath), destination, template.ID, targetId) != null;
    }

    private bool DoArchiveItems(IEnumerable<Item> items, Guid archivalId)
    {
      using (RecursionLimit limit = new RecursionLimit("SqlArchive.ArchiveItem", Sitecore.Configuration.Settings.MaxTreeDepth))
      {
        if (limit.Exceeded)
        {
          return false;
        }
          //archive subitems
        //List<KeyValuePair<Item, ID>> list = new List<KeyValuePair<Item, ID>>();
        
        foreach (Item item in items)
        {
          if (TemplateManager.IsTemplate(item)||TemplateManager.IsTemplatePart(item))
          {
            if (!this.DoArchiveItems(item.Children, archivalId))
            {
              return false;
            }
          }
        }
        //  list.Add(new KeyValuePair<Item, ID>(item, item.ParentID));
        //}

        ReaderWriterLock itemAccessLock = this._database.DataManager.DataEngine.ItemAccessLock;
        if (!itemAccessLock.IsReaderLockHeld)
        {
          itemAccessLock.AcquireWriterLock(-1);
        }

        try
        {
          using (DataProviderTransaction transaction = this._dataApi.CreateTransaction())
          {
            foreach (Item item2 in items)
            {
              if (!this.ArchiveData(item2, archivalId))
              {
                return false;
              }
            }

            transaction.Complete();
          }
        }
        finally
        {
          if (itemAccessLock.IsWriterLockHeld)
          {
            itemAccessLock.ReleaseWriterLock();
          }
        }

        return true;
      }
    }

    private bool EnsureRestoreTarget(Guid archivalId)
    {
      Guid guid;
      string str;
      ArchiveQuery query = new ArchiveQuery();
      query.ArchivalId = archivalId;
      SqlStatement selectStatement = this.GetSelectStatement(query, "{0}ParentId{1}, {0}OriginalLocation{1}");
      using (DataProviderReader reader = this.DataApi.CreateReader(selectStatement.Sql, selectStatement.GetParameters()))
      {
        if (!reader.Read())
        {
          return false;
        }
        
        guid = this._dataApi.GetGuid(0, reader);
        str = this._dataApi.GetString(1, reader).Split(new char[]{':'})[1];
        
      }

      ChangeTrackerArchiveEntry entry = this.GetSingleEntry(archivalId);
      foreach (string s in entry.Templates.Split(new char[] { '|' }))
      {

      }
      //if (this._database.GetItems(new ID(guid))==null)
      //{
      //  guid = ID.NewID.ToGuid();
      //}
      if ((guid == Guid.Empty) || string.IsNullOrEmpty(str))
      {
        return false;
      }

      return this._database.GetItem(new ID(guid)) != null || this.CreateRestoreTarget(new ID(guid), str);
    }

    
  }
}

